from models import Product, Sensor
from public import SQL_Conn
from pandas import DataFrame
import pandas as pd

# region 产品数据
"""
table_name: product
# 产品id:id
# 产品标识：label
# 产品名称：name
# 项目名称：project
# 日期：start_date
"""


def change_product(product: Product):
    """保存产品信息的变化，新增或更新"""
    if type(product) is Product:
        if product.id:
            update_product(product)
        else:
            pro_id = get_max_id("product")
            add_product(product.label, product.name, product.project, product.start_date)
            product_id = get_max_id("product")
            if pro_id == product_id:
                return None
            product.id = product_id
        return 1
    else:
        return 0


# 增
def add_product(label, name, project, start_date):
    """向数据库中添加产品信息"""
    sql_str = "insert into product (label, name,  project, start_date) values (?,?,?,?)"
    tread_one(sql_str, (label, name, project, start_date))


# 改
def update_product(product: Product):
    """更新数据库中的产品信息"""
    sql_str = f"update product set label ='{product.label}',  project ='{product.project}', name='{product.name}', start_date='{product.start_date}'  where id ={product.id}"
    return tread_one(sql_str)


# 查
def find_product(col_name, value):
    sql_str = f"select * from product where {col_name}='{value}'"
    return find_data(sql_str)


# endregion


# region 参数设置数据

"""
table_name: sensor_set
id: int = 0   id
sensor_code: str = None： 传感器的编号
location: str = None  :放置的位置
agree: str = None  放置角度
product_id: str = None  产品的id
"""


def change_sensor_set_list(sensor_list):
    for sensor in sensor_list:
        change_sensor_set(sensor)
    return 1


def change_sensor_set(sensor: Sensor):
    if type(sensor) is Sensor:
        if sensor.id:
            update_sensor_set(sensor)
        else:
            pro_id = get_max_id("sensor_set")
            add_sensor_set(sensor)
            _id = get_max_id("sensor_set")
            if pro_id == _id:
                return None
            sensor.id = _id
        return 1
    else:
        return 0


# 增
def add_sensor_set(sensor):
    sql_str = "insert into sensor_set (sensor_code, location,  agree, product_id) values (?,?,?,?)"
    tread_one(sql_str, (sensor.sensor_code, sensor.location, sensor.agree, sensor.product_id))


# 改
def update_sensor_set(sensor):
    sql_str = f"update sensor_set set sensor_code ='{sensor.sensor_code}',  location ='{sensor.location}', agree='{sensor.agree}', product_id='{sensor.product_id}'  where id ={sensor.id}"
    return tread_one(sql_str)


# 查
def find_sensor_set(product_id, sensor_code=None):
    sql_str = f"select * from sensor_set where product_id='{product_id}'"
    if sensor_code:
        sql_str += f" and sensor_code = '{sensor_code}'"
    return find_data(sql_str)


# endregion


# region 测量数据保存
def add_measure_data(df_data: DataFrame):
    insert_data_from_df('sensor_data', df_data)


def add_treated_data(df_treated_data: DataFrame):
    insert_data_from_df('sensor_treated_data', df_treated_data)


def add_agree_data(df_agree_data: DataFrame):
    insert_data_from_df('agree_data',df_agree_data)


def add_sensor_start_degree(df_data: DataFrame):
    insert_data_from_df('sensor_start_agree',df_data)



# endregion

# region 基础SQL
def get_max_id(table_name):
    sql_str = f'SELECT MAX(id) FROM {table_name}'
    result = find_data(sql_str)
    if result:
        return result[0][0]
    return 0


def tread_many(sql_str, para):
    conn = SQL_Conn().conn
    c = conn.cursor()
    c.executemany(sql_str, para)
    result = conn.commit()
    c.close()
    return result


def tread_one(sql_str, data=None):
    conn = SQL_Conn().conn
    cur = cmd_sql(sql_str, data)
    result = conn.commit()
    cur.close()
    return result


def cmd_sql(sql_str: str, para=None):
    conn = SQL_Conn().conn
    c = conn.cursor()
    if para:
        c.execute(sql_str, para)
        return c
    else:
        c.execute(sql_str)
        return c


def find_data(sql_str: str, para=None):
    """
    查找数据
    Args:
        sql_str: sql语句
        para: 查询的数据
    Returns:查询到的数据
    """
    c = cmd_sql(sql_str, para)
    data = c.fetchall()
    c.close()
    return data


# endregion


def insert_data_from_df(table_name, df_data: DataFrame):
    conn = SQL_Conn().conn
    df_data.to_sql(name=table_name, con=conn, index=True, if_exists="append")


def df_read_data(sql_str) -> DataFrame:
    conn = SQL_Conn().conn
    return pd.read_sql(sql_str,conn)

def get_start_agree_df(product_id) -> DataFrame:
    """
        根据产品ID 获取角度的初始值
    """
    sql_str = f"select * from sensor_start_agree where product_id = {product_id}"
    return  df_read_data(sql_str=sql_str)
